﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportInvoiceInfo : ExportPageBase
    {

        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部导出发票.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "Sheet1";
            }
        }


        #region 属性 (查询条件)


        protected string JingXiaoShang
        {
            get
            {
                return GetRequestValue<string>("JingXiaoShang", "");

            }
        }
        protected string HeTongHao
        {
            get
            {
                return GetRequestValue<string>("HeTongHao", "");

            }
        }

        protected string JiHao
        {
            get
            {
                return GetRequestValue<string>("JiHao", "");

            }
        }

        protected string JiQiState
        {
            get
            {
                return GetRequestValue<string>("JiQiState", "-");
            }
        }
        protected string XiaoShouLeiXing
        {
            get
            {
                return GetRequestValue<string>("XiaoShouLeiXing", "");
            }

        }
        protected string YingYeJingLi
        {
            get
            {
                return GetRequestValue<string>("YingYeJingLi", "");
            }

        }
        protected string XiaoShouShiJianFrom
        {
            get
            {
                return GetRequestValue<string>("XiaoShouShiJianFrom", "");

            }
        }
        protected string XiaoShouShiJianTo
        {
            get
            {
                if (string.IsNullOrEmpty(XiaoShouShiJianFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("XiaoShouShiJianTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(XiaoShouShiJianFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }

        protected string CreateDateFrom
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本周周一
                DateTime endWeek = startWeek.AddDays(6); //本周周日
                return startWeek.ToString("yyyy-MM-dd");
            }
        }
        protected string CreateDateTo
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本周周一
                DateTime endWeek = startWeek.AddDays(6); //本周周日
                return endWeek.ToString("yyyy-MM-dd");

            }
        }


        #endregion
        /// <summary>
        /// 排序字段 值为 id
        /// </summary>
        protected override string orderField
        {
            get
            {
                return string.IsNullOrEmpty(base.orderField) ? "JiHao" : base.orderField;
            }
        }
        /// <summary>
        ///  排序方式 desc或 asc
        /// </summary>
        protected override string orderDirection
        {
            get
            {
                return string.IsNullOrEmpty(base.orderDirection) ? "DESC" : base.orderDirection;
            }
        }
        protected override string SQLSort
        {
            get
            {
                if (orderField == "JiHao")
                {
                    return orderField + " " + orderDirection;
                }
                return (orderField + " " + orderDirection + "  ,FTradeID DESC ");
            }
        }
        protected string GetSQLWhere()
        {
            string wheresql = "";
            string sqlcommonFiled = "";
            if (!string.IsNullOrEmpty(JingXiaoShang))
            {
                sqlcommonFiled = QueryOperatorsEnumHelper.ConvertCommonOperator("JingXiaoShang", QueryOperatorsEnum.等于, JingXiaoShang);
                wheresql = sqlcommonFiled;
            }



            if (!string.IsNullOrEmpty(HeTongHao))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("HeTongHao", QueryOperatorsEnum.等于, HeTongHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            if (!string.IsNullOrEmpty(YingYeJingLi))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("YingYeJingLi", QueryOperatorsEnum.等于, YingYeJingLi);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(XiaoShouShiJianFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("XiaoShouShiJian", QueryOperatorsEnum.范围, XiaoShouShiJianFrom + "|" + XiaoShouShiJianTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            //string sqlAgentName = "";
            //if (!string.IsNullOrEmpty(ShowAgentName))
            //{
            //    sqlAgentName = "'" + ShowAgentName + "'";
            //    sqlAgentName = sqlAgentName.Replace(",", "','");
            //    sqlAgentName = " agentname in (" + sqlAgentName + ")";
            //    if (!string.IsNullOrEmpty(wheresql))
            //    {
            //        wheresql += " and " + sqlAgentName;
            //    }
            //    else
            //    {
            //        wheresql = sqlAgentName;
            //    }
            //}

            string dutySQL = "";
            if (!string.IsNullOrEmpty(JiHao))
            {
                dutySQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiHao", QueryOperatorsEnum.包含, JiHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + dutySQL;
                }
                else
                {
                    wheresql = dutySQL;
                }
            }

            if (JiQiState != "-")
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiQiState", QueryOperatorsEnum.等于, JiQiState);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            if (XiaoShouLeiXing != "")
            {
                string[] xslxs = XiaoShouLeiXing.Split(new char[] { ',' });
                string xslxsql = " XiaoShouLeiXing in (''";
                foreach (string itm in xslxs)
                {
                    if (!string.IsNullOrEmpty(itm))
                    {
                        xslxsql += string.Format(",'{0}'", itm);
                    }
                }
                xslxsql += ")";

                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + xslxsql;
                }
                else
                {
                    wheresql = xslxsql;
                }
            }

            return wheresql;
        }


        private DataTableCollection _dts = null;
        protected DataTableCollection dts
        {
            get
            {
                if (_dts == null)
                {                  
                    _dts = FTradeReportController.FTradeExportGetInvoiceInfo(GetSQLWhere());
                }
                return _dts;
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //
            DataTable dtList = dts[0];

            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            //
            ICellStyle style3 = hssfworkbook.CreateCellStyle();
            style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;//居中 
            style3.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
            style3.WrapText = true;//自动换行
            //

            foreach (DataRow listDTDR in dtList.Rows)
            {
                int cellReal = 0;
                HSSFRow writerow = (HSSFRow)sheet1.CreateRow(rowReal);
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["HeTongHao"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(Convert.ToDateTime(listDTDR["XiaoShouShiJian"]).ToString("yyyy-MM-dd"));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["YingYeJingLi"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["JingXiaoShang"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["JiHao"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(Convert.ToInt32(listDTDR["ShuLiang"]));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["JiQiState"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["XingHao"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["Price"]));
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["GPSFeiYong"]));
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["YueCuXiaoJinE"]));
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["HeTongJinE"]));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["DaoQiRi"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["YiShouKuanXiang"]));
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["WeiShouKuanXiang"]));
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["YuQiKuanXiang"]));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["XiaoShouLeiXing"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FuKuanTiaoKuan"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZDXiaoShouState"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZDXiaoShouShiJian"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZDYongHu"].ToString());
                //writerow.CreateCell(cellReal++).SetCellValue(listDTDR["UpKeepJinE"].ToString());
                //普票信息   
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FPDate"].ToString().Replace("@","\n").Replace("T00:00:00","").Replace("N/>","").Replace("<",""));
                writerow.GetCell(cellReal-1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FPDaiMa"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FPHaoMa"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FPJinE"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FPAcceptName"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FPRemark"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                //专票信息
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZPFPDate"].ToString().Replace("@", "\n").Replace("T00:00:00", "").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZPFPDaiMa"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZPFPHaoMa"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZPFPJinE"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZPFPAcceptName"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["ZPFPRemark"].ToString().Replace("@", "\n").Replace("N/>", "").Replace("<", ""));
                writerow.GetCell(cellReal - 1).CellStyle = style3;

                //writerow.CreateCell(cellReal++).se("2222\n3343243");
                rowReal++;
            }

            HSSFRow writerowSum = (HSSFRow)sheet1.GetRow(0);
            // writerowSum.CreateCell(0).SetCellValue("总计");
            writerowSum.CreateCell(9).SetCellFormula(string.Format("sum(J3:J{0})", rowReal));
            writerowSum.CreateCell(10).SetCellFormula(string.Format("sum(K3:K{0})", rowReal));
            writerowSum.CreateCell(11).SetCellFormula(string.Format("sum(L3:L{0})", rowReal));
            writerowSum.CreateCell(13).SetCellFormula(string.Format("sum(N3:N{0})", rowReal));
            writerowSum.CreateCell(14).SetCellFormula(string.Format("sum(O3:O{0})", rowReal));
            writerowSum.CreateCell(15).SetCellFormula(string.Format("sum(P3:P{0})", rowReal));
            //writerowSum.CreateCell(2).SetCellFormula(string.Format("sum(C3:C{0})", rowReal));          

            //
            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
        }

        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }
        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("应收帐款发票信息{0}.xls", DateTime.Now.ToString("yyyy-MM-dd"));
        }
    }
}